﻿Imports DocumentFormat.OpenXml.Spreadsheet
Imports FineUI
Imports Kimmo
Imports Microsoft.VisualBasic.CompilerServices
Imports System.Collections
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlServerCe
Imports System.Diagnostics
Imports System.Runtime.CompilerServices

Public Class clsTables
    Property ID As Integer = 0
    Property ProjectID As Integer = 0
    Property ProjectName As String = ""
    Property TableName As String = ""
    Property LastUpdated As Nullable(Of DateTime)
    Property UpdatedBy As String = ""
    Property IsNew As Boolean = False
    Property FieldsToUpdate As String = "ID,ProjectID,ProjectName,TableName,LastUpdated,UpdatedBy"
    Property DateFields As String = "LastUpdated"
    Property Cube As String = "Tables"

    Public Shared Sub UpdateProjectName(ByVal strTableID As String, ByVal strTableName As String)
        SQLCompact.RunQuery("update [Tables] set [ProjectName] = '" & strTableName & "' where ProjectID = " & strTableID)
    End Sub

    Public Shared Sub Tables_Blank(ByVal strProjectID As String)
        Dim clsA As New clsTables
        clsA.ID = -1
        clsA.IsNew = True
        clsA.ProjectID = Val(strProjectID)
        clsA.ProjectName = clsProjects.ProjectNameByID(strProjectID)
        clsA.TableName = clsA.ProjectName & " New Table "
        clsA.LastUpdated = Today.Date
        clsA.UpdatedBy = ""
        clsA.Save()
        clsA.TableName = clsA.TableName & " " & clsA.ID.ToString
        clsA.Save()
        clsDL.SaveSetting("projectid", clsA.ProjectID)
        clsDL.SaveSetting("view", "tables")
    End Sub

    Public Sub New()
        MyBase.New()
        Me.ID = -1
        Me.ProjectID = 0
        Me.ProjectName = ""
        Me.TableName = ""
        Me.LastUpdated = Nothing
        Me.UpdatedBy = ""
        Me.Cube = "Tables"
    End Sub

    Public Shared Function ProjectNameByID(ByVal ID As String) As String
        Dim mv As String = SQLCompact.RecordReadToMv("Tables", "ID", ID.ToString(), "ProjectName", ";", "Long")
        Return mv
    End Function

    Public Shared Function ProjectIDByID(ByVal ID As String) As String
        Dim mv As String = SQLCompact.RecordReadToMv("Tables", "ID", ID.ToString(), "ProjectID", ";", "Long")
        Return mv
    End Function

    Public Shared Function ProjectPathByID(ByVal ID As String) As String
        Dim mv As String = SQLCompact.RecordReadToMv("Tables", "ID", ID.ToString(), "ProjectID", ";", "Long")
        Dim pp As String = SQLCompact.RecordReadToMv("Projects", "ID", mv, "ProjectPath", ";", "long")
        Return pp
    End Function

    Public Shared Function TableNameByID(ByVal ID As String) As String
        Dim mv As String = SQLCompact.RecordReadToMv("Tables", "ID", ID.ToString(), "TableName", ";", "Long")
        Return mv
    End Function

    Public Shared Sub DeleteByID(ByVal StrID As String)
        SQLCompact.RunQuery("delete from [Tables] where ID = " & Val(StrID), True)
    End Sub

    Public Sub Read(ByVal sID As String)
        Try
            Dim xset As New DataSet()
            xset = SQLCompact.RecordSeek("Tables", "ID", sID, "*", "Long")
            Dim xcount As Boolean = xset.Tables(0).Rows.Count - 1 <> -1
            If xcount Then
                Dim row As DataRow = xset.Tables(0).Rows(0)
                Me.ID = Val(row("ID").ToString())
                Me.ProjectID = Val(row("ProjectID").ToString)
                Me.ProjectName = row("ProjectName").ToString()
                Me.TableName = row("TableName").ToString()
                xcount = Microsoft.VisualBasic.CompilerServices.Operators.CompareString(row("LastUpdated").ToString(), "", False) = 0
                If Not xcount Then
                    Me.LastUpdated = CDate(row("LastUpdated"))
                Else
                    Me.LastUpdated = Nothing
                End If
                Me.UpdatedBy = row("UpdatedBy").ToString()
                Me.IsNew = False
                xset.Dispose()
            Else
                Me.ID = -1
                Me.ProjectID = 0
                Me.ProjectName = ""
                Me.TableName = ""
                Me.UpdatedBy = ""
                Me.LastUpdated = Nothing
                Me.IsNew = True
            End If
        Catch exception1 As Exception
            Throw New Exception(exception1.Message)
        End Try
    End Sub

    Public Sub Save()
        Dim strCommand As String = ""
        Try
            Dim tableName As String = "Tables"
            Dim fldName As String = "ID"
            If Me.ID = -1 Then
                Me.ID = Val(SQLCompact.TableMaxValue(tableName, fldName, "")) + 1
                Me.IsNew = True
            Else
                Me.IsNew = False
            End If
            If Me.FieldsToUpdate = "*" Then Me.FieldsToUpdate = SQLCompact.Table_FieldNames(tableName, False, ",")
            Me.FieldsToUpdate = clsDL.MvRemoveDuplicates(Me.FieldsToUpdate, ",")
            Dim str2 As String = clsDL.MvPrefixBy(Me.FieldsToUpdate, "@", ",")
            If Me.IsNew = True Then
                strCommand = clsDL.BuildInsertCommand(tableName, Me.FieldsToUpdate, ",")
            Else
                strCommand = clsDL.BuildUpdateCommand(tableName, Me.FieldsToUpdate, fldName, ",")
            End If
            Dim spFlds() As String = {}
            Dim command As SqlCeCommand = SQLCompact.OpenCommand(strCommand)
            Dim totFlds As Integer = clsDL.StrParse(spFlds, Me.FieldsToUpdate, ",")
            command.Parameters.Clear()
            For fldCnt As Integer = 1 To totFlds
                Dim strSearch As String = spFlds(fldCnt)
                Dim d As Boolean = clsDL.MvSearch(Me.DateFields, strSearch, ",") > 0
                If Not d Then
                    command.Parameters.AddWithValue(String.Concat("@", strSearch), RuntimeHelpers.GetObjectValue(RuntimeHelpers.GetObjectValue(RuntimeHelpers.GetObjectValue(clsDL.PropertyGet(Me, strSearch)))))
                Else
                    Dim flag As Boolean = Strings.Len(strSearch) = 0
                    If Not flag Then
                        command.Parameters.AddWithValue(String.Concat("@", strSearch), RuntimeHelpers.GetObjectValue(RuntimeHelpers.GetObjectValue(RuntimeHelpers.GetObjectValue(Interaction.IIf(Information.IsDate(RuntimeHelpers.GetObjectValue(RuntimeHelpers.GetObjectValue(RuntimeHelpers.GetObjectValue(clsDL.PropertyGet(Me, strSearch))))), Conversions.ToDate(RuntimeHelpers.GetObjectValue(clsDL.PropertyGet(Me, strSearch))), DBNull.Value)))))
                    Else
                        command.Parameters.AddWithValue(String.Concat("@", strSearch), DBNull.Value)
                    End If
                End If
            Next
            command.ExecuteNonQuery()
            SQLCompact.Connection.Close()
            command.Dispose()
        Catch exception1 As Exception
            Throw New Exception(exception1.Message)
        End Try
    End Sub

    Public Shared Sub ShowTables(ByRef grdViews As FineUI.Grid, ByVal strFilter As String)
        clsFineUI.Grid.Clear(grdViews, "", "")
        clsFineUI.Grid.TableName = "Tables"
        clsFineUI.Grid.DataKeyNames = "ID"
        clsFineUI.Grid.PageSize = 50
        clsFineUI.Grid.AllowPaging = True
        clsFineUI.Grid.AllowSorting = True
        clsFineUI.Grid.LoadMask = False
        clsFineUI.Grid.Title = "Tables"
        clsFineUI.Grid.ShowBorder = False
        clsFineUI.Grid.ShowHeader = False
        clsFineUI.Grid.EnableRowNumber = False
        clsFineUI.Grid.AutoHeight = True
        clsFineUI.Grid.AutoPostBack = False
        clsFineUI.Grid.EnableCheckBoxSelect = True
        clsFineUI.Grid.AutoWidth = True
        clsFineUI.Grid.EnableAlternateRowColor = True
        clsFineUI.Grid.EnableCollapse = True
        clsFineUI.Grid.EnableMouseOverColor = True
        clsFineUI.Grid.EnableMultiSelect = True
        clsFineUI.Grid.EnableViewState = True
        clsFineUI.Grid.EnableAjax = True
        clsFineUI.Grid.ColumnLines = True
        clsFineUI.Grid.ToolTipField = "TableName"
        clsFineUI.Grid.IsDatabasePaging = False
        clsFineUI.Grid.ShowGridHeader = True
        clsFineUI.Grid.EnableHeaderMenu = True
        clsFineUI.Grid.EnableColumnHide = True
        clsFineUI.Grid.AddColumn("ID", "ID", "{0}", "", "", False, "30", TextAlign.Left, False)
        clsFineUI.Grid.AddTextBox("TableName", "Table Name", "{0}", "", "310", TextAlign.Left, False)
        clsFineUI.Grid.AddColumn("LastUpdated", "Date Updated", "{0:dd/MM/yyyy}", "", "", False, "110", TextAlign.Left, False)
        clsFineUI.Grid.AddColumn("UpdatedBy", "Updated By", "{0}", "", "", False, "110", TextAlign.Left, False)
        clsFineUI.Grid.AddSave()
        clsFineUI.Grid.AddClone()
        clsFineUI.Grid.AddTable()
        clsFineUI.Grid.AddDelete()
        If Len(strFilter) > 0 Then
            clsFineUI.Grid.Filter = strFilter
        End If
        clsFineUI.Grid.OrderBy = "ID"
        clsFineUI.Grid.BuildSelectCommand()
        clsFineUI.Grid.UseSQLCompact = True
        clsFineUI.Grid.Update(Nothing)
    End Sub

    Public Shared Sub TreeLoad(ByRef parentNode As FineUI.TreeNode)
        Dim strNodeID As String = parentNode.NodeID
        Dim strPrefix As String = clsDL.MvField(strNodeID, 1, "-")
        Dim strSuffix As String = clsDL.MvField(strNodeID, 2, "-")
        Select Case strPrefix.ToLower
            Case "project"
                parentNode.Nodes.Clear()
                Dim xTable As DataTable = SQLCompact.OpenDataTable("select ID,TableName from Tables where ProjectID = " & Val(strSuffix) & " order by TableName")
                For Each xRow As DataRow In xTable.Rows
                    Dim strID As String = xRow("ID").ToString
                    Dim strTableName As String = xRow("TableName").ToString
                    Dim pNode As FineUI.TreeNode = clsFineUI.TreeView.AddNode(parentNode, "table-" & strID, strTableName, "list", "table", strID)
                    pNode.NavigateUrl = "frmColumns.aspx?view=columns&tableid=" & strID & "&projectid=" & strSuffix
                    pNode.Target = "rgnTables"
                    ' get source files
                    Dim scTable As DataTable = SQLCompact.OpenDataTable("select ID,FilePath,ClassName from SourceFiles where TableID = " & strID)
                    For Each scRow As DataRow In scTable.Rows
                        Dim scID As String = scRow("ID").ToString
                        Dim scFilePath As String = scRow("FilePath").ToString
                        Dim scClassName As String = scRow("ClassName").ToString
                        Dim scNode As FineUI.TreeNode = clsFineUI.TreeView.AddNode(pNode, "sourcefile-" & scID, scClassName, "sourcecode", "sourcefile", scID)
                        scNode.NavigateUrl = "frmSourceCode.aspx?file=" & scFilePath
                        scNode.Target = "rgnTables"
                        scNode.Leaf = True
                    Next
                Next

        End Select
        
    End Sub

End Class
